﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/// <summary>
/// SQL 的摘要说明
/// </summary>
public class SQL
{
    string sqlconsrt = ConfigurationManager.ConnectionStrings[1].ConnectionString;
    SqlConnection conn;
    SqlCommand comm;
    public SQL()
    {
        conn = new SqlConnection(sqlconsrt);
    }

    public bool LogIn(string id, string paw)
    {
        string excuteSQL = "select password from Reader_info where ReaderID=" + id + "";
        comm = new SqlCommand(excuteSQL, conn);
        conn.Open();
        SqlDataReader SqlDatareader = comm.ExecuteReader();
        while (SqlDatareader.Read())
        {
            if (paw.Equals(SqlDatareader.GetString(0)))
            {
                conn.Close();
                return true;
            }
            else
            {
                conn.Close();
                return false;
            }
        }

        return false;
    }

    public bool BorrowAgain(string readerID, string bookID)
    {
        string borrowTimes = getDataFormTable("XUJIETIMES", "Borrow_books_manage", "bookid", bookID, "readerID", readerID, "", "", 2);

        int days = Convert.ToInt32(borrowTimes);
        if (days>Convert.ToInt32(getDataFormTable("BorrowTimes", "System_state", "", "", "", "", "", "", 0)))
        {
            return false;
        }

        string date = PickUpBorrowTime(readerID, bookID);
        DateTime dt = DateTime.Parse(date);
        int timenow = PickUpBorrowAgainTimes();
        dt = dt.AddDays(timenow);
        string excuteSQL = "update Borrow_books_manage set ShouldReturnDate='" + dt.ToString() + "' where readerId='" + readerID + "' and BookID='" + bookID + "'";
        comm = new SqlCommand(excuteSQL, conn);
        conn.Open();

        if (comm.ExecuteNonQuery() > 0)
        {
            conn.Close();
            excuteSQL = "update Borrow_books_manage set XUJIETIMES=" + (days + 1) + "where readerId='" + readerID + "' and BookID='" + bookID + "'";
            conn.Open();
            comm = new SqlCommand(excuteSQL, conn);
            comm.ExecuteNonQuery();
            return true;
        }
        conn.Close();
        return false;
    }

    public int PickUpBorrowAgainTimes()
    {
        int days = 0;
        string temp="0";
        string excuteSQL = "select borrowdays  from System_state";
        comm = new SqlCommand(excuteSQL, conn);
        conn.Open();
        SqlDataReader SqlDatareader = comm.ExecuteReader();
        while (SqlDatareader.Read())
        {
            temp = SqlDatareader.GetString(0);
        }
        conn.Close();
        return Convert.ToInt32(temp);
    }

    public string PickUpBorrowTime(string readerID, string bookID)
    {
     
        DateTime dt=DateTime.Now ;
        string excuteSQL = "select ShouldReturnDate  from Borrow_books_manage where readerId='" + readerID + "' and BookID='" + bookID + "'";
        comm = new SqlCommand(excuteSQL, conn);
        conn.Open();
        SqlDataReader SqlDatareader = comm.ExecuteReader();
        while (SqlDatareader.Read())
        {
            dt = SqlDatareader.GetDateTime(0);
        }
        conn.Close();
        return dt.ToString();
    }





    public string getDataFormTable(string col, string tablename, string content1, string value1,string content2,  string value2, string content3, string value3, int num)
    {
        string data = "";
        string excuteSQL = "";
        SqlDataReader SqlDatareader;
        switch (num)
        {
            case 0:
                excuteSQL = "select " + col + "  from " + tablename+"";
                comm = new SqlCommand(excuteSQL, conn);
                conn.Open();
                SqlDatareader = comm.ExecuteReader();
                while (SqlDatareader.Read())
                {
                    data = SqlDatareader.GetString(0);
                }
                conn.Close();
                return data;

            case 1: 
                excuteSQL = "select " + col + "  from " + tablename + " where " + content1 + " ='" + value1 + "'";
                comm = new SqlCommand(excuteSQL, conn);
                conn.Open();
                SqlDatareader = comm.ExecuteReader();
                while (SqlDatareader.Read())
                {
                    try
                    {
                        data = SqlDatareader.GetString(0);
                    }
                    catch (System.Exception ex)
                    {
                        try
                        {
                            int i = SqlDatareader.GetInt32(0);
                            conn.Close();
                            return i.ToString();
                        }
                        catch (System.Exception ex2)
                        {
                            DateTime dt = SqlDatareader.GetDateTime(0);
                            conn.Close();

                            return dt.ToShortDateString().ToString();
                        }
                        
                    }
                }
                conn.Close();
                return data;
            case 2:

                excuteSQL = "select " + col + "  from " + tablename + " where " + content1 + " ='" + value1 + "' and " + content2 + "='" + value2 + "'";
               
                conn.Open();
                comm = new SqlCommand(excuteSQL, conn);
                SqlDatareader = comm.ExecuteReader();
                while (SqlDatareader.Read())
                {
                    try
                    {
                        data = SqlDatareader.GetString(0);
                    }
                    catch (System.Exception ex)
                    {

                        int i=SqlDatareader.GetInt32(0);
                        return i.ToString();
                    }
                    
                }
                conn.Close();
                return data;

            case 3:
                excuteSQL = "select " + col + "  from " + tablename + " where " + content1 + " ='" + value1 + "' and " + content2 + "='" + value2 + "' and '" + content3 + "'='" + value3 + "'";
               
                conn.Open();
                SqlDatareader = comm.ExecuteReader();
                while (SqlDatareader.Read())
                {
                    data = SqlDatareader.GetString(0);
                }
                conn.Close();
                return data;
            default:
                return data;
        }

    }


    public string readerLendingData(string year,string readerid)
    {
        int[] data=new int[12];
        string mydata = "";
        
        for (int i = 0; i <= 11; i++)
        {
            string excuteSQL = "select COUNT(*) from Borrow_books_manage where DATEPART(YEAR,LendBookDate) = " + year + " and MONTH(LendBookDate) = " + (i+1) + " and readerid='"+readerid+"'";
            comm = new SqlCommand(excuteSQL, conn);
            conn.Open();
            SqlDataReader SqlDatareader = comm.ExecuteReader();
            while (SqlDatareader.Read())
            {
                data[i] = SqlDatareader.GetInt32(0);
            }
            conn.Close();
            if (i != 11)
                mydata += data[i].ToString() + ",";
            else
                mydata += data[i];
        }

        return mydata;
    }

    public bool setDataIntoTable(string tablename,string col,string content,string value,string ifCOL,string ifVALUE)
    {
        bool flag=false;
        string excuteSQL = "update " + tablename + " set " + col + "='" + value + "' where " + ifCOL + " = '" + ifVALUE + "'";
        comm = new SqlCommand(excuteSQL, conn);
        conn.Open();
        if (comm.ExecuteNonQuery() > 0)
        {
            conn.Close();   
            return true;
        }
        return flag;
    }

    public string EXQSQL(string sql)
    {
        string flag="";
        string excuteSQL = sql;
        comm = new SqlCommand(excuteSQL, conn);
        conn.Open();
        SqlDataReader SqlDatareader = comm.ExecuteReader();
        while (SqlDatareader.Read())
        {
            try
            {
                flag = SqlDatareader.GetString(0);
            }
            catch (System.Exception ex)
            {
                int i = SqlDatareader.GetInt32(0);
                conn.Close();
                return i.ToString();
            }
            
        }
        conn.Close();
        return flag;
    }


    public string EXQSQLgetFloat(string sql)
    {
        string flag = "";
        string excuteSQL = sql;
        comm = new SqlCommand(excuteSQL, conn);
        conn.Open();
        SqlDataReader SqlDatareader = comm.ExecuteReader();
        while (SqlDatareader.Read())
        {
            try
            {
                flag = SqlDatareader.GetString(0);
            }
            catch (System.Exception ex)
            {
                double i = SqlDatareader.GetDouble(0);
                conn.Close();
                return i.ToString();
            }

        }
        conn.Close();
        return flag;
    }
    public DataSet getDataset(string EXSQL,string para)
    {
        DataSet ds = new DataSet();
        conn.Open();
        SqlDataAdapter sqld=new SqlDataAdapter(EXSQL,conn);
        sqld.Fill(ds);
        conn.Close();
        return ds;

    }

    public void updateBOOKinfo(string EXSQL)
    {
        
        string excuteSQL = EXSQL;
        comm = new SqlCommand(excuteSQL, conn);
        conn.Open();
        comm.ExecuteNonQuery();
        conn.Close();
        return ;
    }

    public bool updateAmerce(string sql)
    {
        bool flag = false;
       
        comm = new SqlCommand(sql, conn);
        conn.Open();
        if (comm.ExecuteNonQuery() > 0)
        {
            conn.Close();
            return true;
        }
        conn.Close();
        return flag;
    }





}